---
title: "01-PostgreSQL 基础与 MySQL 对照"
created: 2026-08-31
tags:
  - 项目筑基
---

# PostgreSQL 基础与 MySQL 对照

> PostgreSQL 系统讲解第一篇。我 MySQL 已经过了一遍，所以这组用**对照式学习**——每讲一个点都问"MySQL 里我怎么做、PG 里有什么不一样"。PG 在国外互联网公司和 AI 应用里越来越常见（pgvector 就是它的扩展），值得系统学一遍。

## 为什么 PG 值得系统学

- **功能天花板高**：JSONB、数组、窗口函数、全文检索、自定义类型全是原生能力，很多 MySQL 要插件或绕路的事 PG 内置
- **标准兼容与严谨**：SQL 标准执行得更彻底，事务里 DDL 都能回滚
- **生态强**：pgvector（向量检索）、PostGIS（地理）、TimescaleDB（时序）——本数据库组"03-向量数据库"篇未来实操大概率落在它上面
- **MySQL 兼容不了的场景**：复杂分析查询、GIS、严格的数据完整性要求

## 安装与连接

```bash
brew install postgresql@16          # macOS；Ubuntu 用 apt install postgresql
brew services start postgresql@16   # 启动服务

psql -U zhouwei5 -d postgres        # 本地进入交互终端；默认超级用户=当前系统用户
createdb mydb                       # 建库
psql "postgresql://user:pass@host:5432/dbname"   # 标准 URI 连接
```

> 💡 第一个坑：PG 的"用户"是集群级、库是实例里的一份独立数据空间——`psql` 不带 `-d` 时默认连和用户同名的库，不存在就报错，先 `createdb` 或连 `postgres` 库。

## psql 元命令速记

psql 的反斜杠命令相当于 MySQL 的 `show`/`desc`：

| 命令 | 作用 | MySQL 对应 |
| --- | --- | --- |
| `\l` | 列出所有数据库 | `SHOW DATABASES` |
| `\c dbname` | 切换数据库 | `USE dbname` |
| `\dt` | 列出当前 schema 的表 | `SHOW TABLES` |
| `\d 表名` | 表结构（列/索引/约束） | `DESC 表名` + `SHOW CREATE TABLE` |
| `\di` / `\dv` / `\ds` | 列索引 / 视图 / 序列 | — |
| `\x` | 扩展显示（竖排），宽表救星 | `\G` |
| `\timing` | 显示每条 SQL 耗时 | — |
| `\q` | 退出 | `exit` |

## 与 MySQL 的核心差异总表

| 维度 | MySQL | PostgreSQL |
| --- | --- | --- |
| 标识符大小写 | 表名大小写敏感性看平台 | 未加引号的标识符**一律折叠成小写** |
| 层级 | database ≈ schema | database → **schema**（一个库内多个命名空间）→ table |
| 自增 | `AUTO_INCREMENT` | `SERIAL` / `GENERATED ALWAYS AS IDENTITY`（推荐后者，SQL 标准） |
| 引号语义 | 双引号可用于字符串 | **双引号=标识符**，字符串只用单引号 |
| DDL | 不支持事务回滚 | **DDL 在事务里可回滚**（改错表结构 ROLLBACK 即可） |
| 模糊查询 | `LIKE` 大小写看排序规则 | `ILIKE` 忽略大小写（还有 `~` 正则操作符） |
| 元信息 | information_schema 为主 | information_schema + `\d` 系列 + pg_catalog |
| 日期函数 | `DATE_ADD`、`DATE_SUB` | `INTERVAL '7 days'` 直接参与运算 |

> 💡 最容易撞的坑就是引号和大小写：`SELECT "Name" FROM users` 在 PG 里是找叫 `Name`（大写）的列——而建表时没加引号的话它早被折叠成 `name` 了，于是报"列不存在"。MySQL 转过来的人几乎都撞过一次。

## 基本对象操作对照

```sql
CREATE DATABASE shop;                      -- 与 MySQL 同
CREATE TABLE users (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name      varchar(64) NOT NULL,
    email     varchar(128) UNIQUE,
    tags      text[],                        -- 数组类型！MySQL 没有的
    attrs     jsonb,                         -- JSONB，见 02 篇
    created_at timestamptz DEFAULT now()
);
INSERT INTO users (name, email) VALUES ('张三', 'z@ex.com') RETURNING id;  -- INSERT 也能 RETURNING 取回自增 id
```

我的记忆锚点：**PG 里" 能力都是内建的"——数组、JSONB、全文检索、地理、向量（扩展）**；MySQL 是"够用就好靠生态补"。学 PG 的过程就是把 MySQL 里"绕路"的经验重新对一遍答案。

---
⬅️ [[18-事务与并发控制|18-事务与并发控制]] 🏠 [[00-数据库|00-数据库]] ➡️ [[02-PostgreSQL 数据类型与表设计|02-PostgreSQL 数据类型与表设计]]